﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using System.Collections;

namespace mealplanner.database
{

   public static class IngredientDB
    {
        public static string comm;


        /// <summary>
        /// get the type and cost of ingredient by  id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>

        public static ArrayList getIngredientCostAndCaloriesByIngredientId(int id)
        {
            ArrayList list = new ArrayList();
            // comm = "select ingredient.cost,ingredient.calories from ingredient where ingredient_name = name";
            comm = "select mealplanner.ingredient.cost,mealplanner.ingredient.calories from mealplanner.ingredient where ingredient.ingredient_id = " + id;
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["cost"]);
                list.Add(reader["calories"]);
            }
            Database.con.Close();
            return list;

        }


        /// <summary>
        /// get the type and cost of ingredient by  name
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>

        public static ArrayList getIngredientCostAndCaloriesByIngredientName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "select ingredient.cost,ingredient.calories from ingredient where ingredient_name =\'" + name + "\'";
            //comm = "select mealplanner.ingredient.cost,mealplanner.ingredient.calories from mealplanner.ingredient where ingredient.ingredient_id = " + id;
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["cost"]);
                list.Add(reader["calories"]);
            }
            Database.con.Close();
            return list;


        }


        public static ArrayList getAllMealsNameByIngredientName(string name)
        {
            ArrayList list = new ArrayList();
            comm = "SELECT mealplanner.meal.meal_name FROM mealplanner.meal, mealplanner.ingredient, mealplanner.meal_ingredient WHERE mealplanner.meal.meal_id = mealplanner.meal_ingredient.meal_id AND mealplanner.ingredient.ingredient_id = mealplanner.meal_ingredient.ingredient_id AND mealplanner.ingredient.ingredient_name =\'" + name + "\'";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["meal_name"]);
            }
            Database.con.Close();
            return list;
        }

        public static ArrayList getAllIngredient()
        {
            ArrayList list = new ArrayList();
            comm = "SELECT ingredient_name FROM mealplanner.ingredient";
            MySqlDataReader reader = Database.executeCommand(comm);
            while (reader.Read())
            {
                list.Add(reader["ingredient_name"]);
            }
            Database.con.Close();
            return list;
        }

    }
}
